﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IrisGenerator.Common.DatabaseModel;
using System.Data.SqlClient;
using IrisGenerator.Modules.SqlServer2005.DataAccess;
using IrisGenerator.Common.Core;
using IrisGenerator.Framework.Database;
using IrisGenerator.Common.DataTypes;
using System.Collections.ObjectModel;

namespace IrisGenerator.Modules.SqlServer2005.SqlModel
{
    internal class SqlTable
    {
        internal static ColumnCollection GetColumns(Table table, string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new Exception("Connection string not defined");

            if (table == null)
                throw new Exception("Table not defined");

            ColumnCollection returnValue = null;

            SqlDataReader dr = null;
            try
            {
                dr = SystemProcedures.GetColumnsFromModel(table.TabName, table.TabOwner, connectionString);

                while (dr.Read())
                {
                    if (returnValue == null)
                        returnValue = new ColumnCollection();
                    Column currentColumn;

                    Target target = Target.SelectByCode("SQL2005");
                    if (target == null)
                        throw new Exception("Target SQL2005 not known");


                    //TODO: Implémenter la création de la colonne.
                    currentColumn = new Column();

                    currentColumn.Table = table;
                    currentColumn.ColName = SqlConvert.ToString(dr["col_name"]);
                    currentColumn.ColNullable = SqlConvert.ToBoolean(dr["col_null"]);
                    currentColumn.ColSequential = SqlConvert.ToBoolean(dr["col_identity"]);
                    currentColumn.ColComputed = SqlConvert.ToBoolean(dr["col_iscomputed"]);

                    string sqlType = SqlConvert.ToString(dr["col_basetypename"]);

                    currentColumn.Metatype = Metatype.ConvertToMetatype(
                        sqlType, target.TarId);

                    // Gestion des longueurs de données dans SQLServer...

                    if (!string.IsNullOrEmpty(SqlConvert.ToString(dr["col_scale"])))
                    {
                        //On a affaire à un numérique à virgule floattante...
                        currentColumn.ColLength = SqlConvert.ToString(dr["col_prec"]);

                        int result;
                        if (int.TryParse(SqlConvert.ToString(dr["col_scale"]), out result))
                            currentColumn.ColPrecision = result;

                    }
                    else
                    {
                        if (SqlConvert.ToString(dr["col_len"]).Equals("0") || SqlConvert.ToString(dr["col_len"]).Equals("-1"))
                        {
                            //TODO: What is it for???
                            if ("varchar".Equals(sqlType) || "nvarchar".Equals(sqlType))
                                currentColumn.ColLength = "max";
                            else if ("xml".Equals(sqlType))
                                currentColumn.ColLength = string.Empty;
                        }
                        else
                        {
                            currentColumn.ColLength = SqlConvert.ToString(dr["col_len"]);
                        }
                        int result;
                        if (int.TryParse(SqlConvert.ToString(dr["col_prec"]), out result))
                            currentColumn.ColPrecision = result;
                    }
                    returnValue.Add(currentColumn);

                }
            }
            catch (SqlException sqlex)
            {
                throw new Exception("Exception SQL");
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (dr != null)
                    dr.Dispose();
            }
            return returnValue;
        }

        internal static void GetTablePrimaryKey(Table table, string connectionString)
        {
            SqlDataReader dr = SystemProcedures.GetPrimaryKeyFromTable(table.TabName, table.TabOwner, connectionString);
            Constraint constraint = null;
            try
            {
                while (dr.Read())
                {
                    if (constraint==null ||!constraint.Name.Equals(SqlConvert.ToString(dr["PK_NAME"])))
                    {
                        ConstraintType type = ConstraintType.LoadFromName(ConstraintType.PRIMARY_KEY);
                        constraint = new Constraint();
                        constraint.ConstraintType = type;
                        constraint.Table = table;
                        constraint.Name = SqlConvert.ToString(dr["PK_NAME"]);

                        constraint.Insert();

                    }
                    
                    ConstraintKey key = new ConstraintKey();

                    Column primary = Column.LoadFromName(SqlConvert.ToString(dr["COLUMN_NAME"]));
                    key.Constraint = constraint;
                    key.PrimaryColumn = primary;
                    key.Insert();

                    constraint.Keys.Add(key);
                }
                table.Constraints.Add(constraint);
            }
            catch (SqlException sqlex)
            {
                throw;
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (dr != null)
                    dr.Dispose();


            }
            //return constraint;
        }

        internal static void GetForeignKeys(Table table, string connectionString)
        {
            SqlDataReader dr = SystemProcedures.GetForeignKeysFromTable(table.TabName, table.TabOwner, connectionString);
            Constraint constraint = null;

            try
            {
                while (dr.Read())
                {
                    if (constraint == null || !constraint.Name.Equals(SqlConvert.ToString(dr["FK_NAME"])))
                    {
                        ConstraintType type = ConstraintType.LoadFromName(ConstraintType.FOREIGN_KEY);
                        constraint = new Constraint();
                        constraint.ConstraintType = type;
                        constraint.Table = table;
                        constraint.Name = SqlConvert.ToString(dr["FK_NAME"]);

                        constraint.Insert();
                    }

                    ConstraintKey key = new ConstraintKey();

                    Column primary = table.ColumnCollection.Single<Column>
                        (column => column.ColName.Equals(SqlConvert.ToString(dr["PK_COLUMN_NAME"])));

                    Table foreignTable = table.Database.TableCollection.Single<Table>
                        (tmpTable => tmpTable.TabName.Equals(SqlConvert.ToString(dr["FK_TABLE_NAME"])));

                    if (foreignTable != null)
                    {
                        Column foreign = foreignTable.ColumnCollection.Single<Column>
                            (column => column.ColName.Equals(SqlConvert.ToString(dr["FK_COLUMN_NAME"])));
                        if (foreign != null)
                        {
                            key.ForeignColumn = foreign;
                        }
                        else
                        {
                            //TODO: There is an exception to handle
                        }
                    }
                    else
                    {
                        //TODO: There is an exception to handle
                    }

                    key.Constraint = constraint;
                    key.PrimaryColumn = primary;
                    key.Insert();

                    constraint.Keys.Add(key);
                    table.Constraints.Add(constraint);

                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (dr != null)
                    dr.Dispose();
            }
        }

    }
}
